﻿<%
'''MSSQL数据库模型驱动类
Class AshapoModelDriverMssql
	
	
	'当前数据库对象
	Private s_db
	
	'当前表模型(为空表示数据库全局)
	Private s_table
	
	'当前表一级前缀
	Private s_prefix
	
	'当前数据表别名(为空表示不需要)
	Private s_alias
	
	'top值(为空表示不需要使用TOP)
	Private s_top

	'Distinct值(为空表示不需要使用DISTINCT)
	Private s_distinct
	
	'field值(要查询的字段)
	Private s_field
	
	'join值(要进行JOIN的完整语句)
	Private s_join
	
	'union值(含UNION 和 UNION ALL)
	Private s_union
	
	'where(当前条件定义)
	Private s_where
	
	'order(当前排序定义)
	Private s_order

	'分页时指定的每页数量
	Private s_size

	'分页时指定的当前页码
	Private s_page

	'最后一次查询语句取得的记录条数
	Private s_RecordCount

	'最后一次查询语句取得的分页总数
	Private s_PageCount

	'进行查询的目标SQL语句段，如table指定，LEFT JOIN、 UNION等
	Private s_target

	'进行GROUP的SQL语句段
	Private s_group

	''GROUP语句的HAVING自语句
	Private s_having
	
	'当前的组装SQL语句
	Private s_sql
	
	'''内部使用的AshapoConfig对象
	Private s_cfg

	'''自增ID字段名，默认是id
	Private s_auto_field
	
	'''构造
	Private Sub Class_Initialize()
		Set s_cfg = New AshapoConfig
		Set s_db = New AshapoDatabase

		s_table = ""
		s_prefix = ""
		s_alias = ""
		s_top = ""
		s_distinct = ""
		s_field = "*"
		s_join = ""
		s_union = ""
		s_where = ""
		s_order = ""
		s_size = 10
		s_page = 0
		s_RecordCount = 0
		s_PageCount = 1

		s_target = ""
		s_group = ""
		s_having = ""
		s_sql = ""

		s_auto_field = "id"
	End Sub
	
	'''析构
	Private Sub Class_Terminate()
		Set s_db = Nothing
		Set s_cfg = Nothing
	End Sub

	'''初始化
	'p_server:MSSQL服务器
	'p_port:MSSQL端口
	'p_database:指定数据库
	'p_user:账户
	'p_pwd:密码
	Public Sub Init(Byval p_server, Byval p_port, Byval p_database, Byval p_user, Byval p_pwd)
		Dim t_provider
		If p_port = "" Then
			t_provider = "Provider=sqloledb;Data Source=" & p_server & ";Initial Catalog=" & p_database & ";User Id=" & p_user & ";Password=" & p_pwd & ";"
		Else
			t_provider = "Provider=sqloledb;Data Source=" & p_server & "," & p_port & ";Initial Catalog=" & p_database & ";User Id=" & p_user & ";Password=" & p_pwd & ";"
		End If
		s_db.Init( t_provider )
	End Sub

	'''指定自增ID字段名
	'p_field:字段名
	Public Property Let AutoField(Byval p_field)
		s_auto_field = p_field
	End Property
	
	'''获取当前的数据库原型
	Public Property Get DataBase()
		Set DataBase = s_db
	End Property

	
	'''设置当前表,默认使用
	'p_name:不含各类前缀的表名
	Public Function Table(Byval p_name)
		s_table = p_name
		Set Table = Me
	End Function
	
	'''设置表的前缀
	'p_prefix:表前缀
	Public Function Prefix(Byval p_prefix)
		s_prefix = p_prefix
		Set Prefix = Me
	End Function
	
	'''给当前数据表定义别名
	Public Function Alias(Byval p_alias)
			s_alias = " AS " & p_alias
		Set Alias = Me
	End Function
	
	'''查询前几个
	'p_number:要获取的个数
	Public Function Top(Byval p_number)
		s_top = "TOP " & CStr(p_number) & " "
		Set Top = Me
	End Function

	'''用于返回唯一不同的值 。
	'p_bool:是否返回唯一不同的值 。
	Public Function Distinct(Byval p_bool)
		If p_bool Then
			s_distinct = " DISTINCT "
		Else
			s_distinct = ""
		End If
		Set Distinct = Me
	End Function
	
	'''指定查询字段
	'p_fields:要指定的字符串或数组
	Public Function Field(Byval p_fields)
		'''指定字段时如果之前存在*匹配符则删去
		'''todo 待修改
		If s_field = "*" Then
			s_field = ""
		End If
		If IsArray(p_fields) Then
			Dim t_field
			For Each t_field In p_fields
				If Has(s_field) Then
					s_field = s_field & ", "
				End If
				s_field = s_field & t_field
			Next
		Else
			s_field = p_fields
		End If
		Set Field = Me
	End Function
	
	'''查询SQL组装join,可以多次调用或者传入数组参数以Join多张表
	'''notice:本方法是顺序有关的
	'p_joins:要组装的SQL语句或者SQL语句块组成的数组
	Public Function [Join](Byval p_joins)
		If IsArray(p_joins) Then
			Dim t_join
			For Each t_join In p_joins
				If s_target = "" Then
					s_target = "[" & s_prefix & s_table & "]" & s_alias & " " & t_join
				Else
					s_target = s_target & " " & t_join
				End If
			Next
		Else
			If s_target = "" Then
				s_target = "[" & s_prefix & s_table & "]" & s_alias & " " & p_joins
			Else
				s_target = s_target & " " & p_joins 
			End If
		End If
		Set [Join] = Me
	End Function

	'''UNION组装,可以多次调用或者传入数组参数以Union多张表
	'''notice:Union语句正常情况下应再Join语句后执行
	'p_unions:要组装的SQL语句或者SQL语句块组成的数组
	Public Function Union(Byval p_unions)
		If s_target = "" Then
			s_target = "[" & s_prefix & s_table & "]" & s_alias
		End If
		If IsArray(p_unions) Then
			Dim t_union
			For Each t_union In p_unions
				s_target = s_target & " UNION " & t_union
			Next
		Else
			s_target = s_target & " UNION " & p_unions
		End If
		Set Union = Me
	End Function

	'''Union All组装,可以多次调用或者传入数组参数以UnionAll多张表
	'''notice:UnionAll语句正常情况下应再Join语句后执行
	'p_sql:要UNION ALL的SQL语句段
	Public Function UnionAll(Byval p_unions)
		If s_target = "" Then
			s_target = "[" & s_prefix & s_table & "]" & s_alias
		End If
		If IsArray(p_unions) Then
			Dim t_union
			For Each t_union In p_unions
				s_target = s_target & " UNION ALL " & t_union
			Next
		Else
			s_target = s_target & " UNION ALL " & p_unions
		End If
		Set UnionAll = Me
	End Function
	
	'''指定查询条件
	Public Function Where(Byval p_s)
		s_where = p_s
		Set Where = Me
	End Function

	'''如果Where条件中含变量替换符，则使用该方法进行替换
	Public Function Variable(Byval p_variables)
	End Function
	
	'''对结果排序
	Public Function Order(Byval p_s)
		If p_s <> "" Then
			s_order = " ORDER BY " & p_s
		End If
		Set Order = Me
	End Function

	'''设置分页时每页记录数量
	'''notice，注意配置后接下来的Size都将以该值作为默认值
	'p_size:每页记录数量
	Public Function Size(Byval p_size)
		s_size = p_size
		Set Size = Me
	End Function

	'''分页查询
	'p_i:指定页码
	Public Function Page(Byval p_page)
		s_page = p_page
		Set Page = Me
	End Function

	'''GROUP BY 语句
	'''notice:不允许多次调用，请直接指定字段，多个字段以半角逗号隔开
	''p_fieLd:要进行GROUP BY的字段
	Public Function Group(Byval p_field)
		s_group = " GROUP BY " & p_field
		Set Group = Me
	End Function

	''''GROUP的HAVING自语句
	Public Function Having(Byval p_condition)
		s_having = " HAVING " & p_condition
		Set Having = Me
	End Function

	'''创建最终的执行SQL
	Private Sub buildSQL_(Byval p_action)
		If s_target = "" Then
			s_target = "[" & s_prefix & s_table & "]" & s_alias
		End If
		If s_where = "" Then
			s_sql = "SELECT " & s_distinct & s_top & s_field & " FROM " & s_target & s_group & s_having & s_order
		Else
			s_sql = "SELECT " & s_distinct & s_top & s_field & " FROM " & s_target &" WHERE " & s_where & s_group & s_having & s_order
		End If
		'Die(s_sql)
	End Sub

	'''重设各参数以便下次再赋值执行
	'''todo 需要修改
	Private Sub reSetParams_()

		'''notice:持续操作中s_table、s_prefix是不需要制空的
		''s_table = ""
		''s_prefix = ""

		s_alias = ""
		s_top = ""
		s_field = "*"
		s_join = ""
		s_union = ""
		s_where = ""
		s_order = ""

		'''todo s_size需要保持原来的默认值
		s_size = 10

		s_page = 0

		s_target = ""
		s_group = ""
		s_having = ""
	End Sub
	
	'''查询数据集,返回结果数组
	Public Function [Select]()
		'Die(s_page)
		buildSQL_("SELECT")
		'返回数组
		Dim t_rs : Set t_rs = s_db.Query(s_sql)
		If t_rs.Eof Then
			[Select] = Array()
			Exit Function
		End If
		s_RecordCount = t_rs.RecordCount
		Dim t_arr
		ReDim t_arr(-1)
		Dim t_x, t_i : t_i = 0
		If s_page > 0 Then
			t_rs.PageSize = s_size
			s_PageCount = t_rs.PageCount
			If s_page > s_PageCount Then
				s_page = s_PageCount
			End If
			t_rs.AbsolutePage = s_page
			For t_i=0 To t_rs.PageSize-1
				If t_rs.Eof Then
					Exit For
				Else
					ReDim Preserve t_arr(t_i)
					Set t_arr( t_i ) = Server.CreateObject( s_cfg.Environment("Scripting.Dictionary") )
					For Each t_x In t_rs.Fields
						t_arr( t_i )( t_x.Name ) = t_x.Value
					Next
					t_rs.MoveNext()
				End If
			Next
		Else
			ReDim t_arr(t_rs.RecordCount-1)
			While Not t_rs.Eof
				Set t_arr( t_i ) = Server.CreateObject( s_cfg.Environment("Scripting.Dictionary") )
				For Each t_x In t_rs.Fields
					t_arr( t_i )( t_x.Name ) = t_x.Value
				Next
				t_i = t_i + 1
				t_rs.MoveNext()
			Wend
		End If
		s_db.Close(t_rs)
		reSetParams_()
		[Select] = t_arr
	End Function
	
	'''取得最后一个搜索语句的总记录条数
	'''notice:使用分页也是返回总的记录数
	Public Function [Count]()
		If s_RecordCount = 0 Then
			[Select]()
		End If
		[Count] = s_RecordCount
	End Function

	'''取得最后一个搜索语句的总记录页数
	Public Property Get Pages()
		Pages = s_PageCount
	End Property
	
	'''查询单条数据
	Public Function [Find]()
		Top(1)
		Dim t_rows : t_rows = [Select]()
		If IsN(t_rows) Then
			Set [Find] = Nothing
			Exit Function
		End If
		Set [Find] = t_rows(0)
	End Function
	
	'''获取记录最大值
	'p_field:要获取的字段名
	Public Function Max(Byval p_field)
		Dim t_row
		Field("MAX(" & p_field & ") AS max_" & p_field)
		Set t_row = Find()
		Max = t_row("max_" & p_field)
	End Function

	'''获取记录最小值
	'p_field:要获取的字段名
	Public Function Min(Byval p_field)
		Dim t_row
		Field("MIN(" & p_field & ") AS min_" & p_field)
		Set t_row = Find()
		Min = t_row("min_" & p_field)
	End Function

	'''获取记录平均值
	'p_field:要获取的字段名
	Public Function Avg(Byval p_field)
		Dim t_row
		Field("AVG(" & p_field & ") AS avg_" & p_field)
		Set t_row = Find()
		Avg = t_row("avg_" & p_field)
	End Function

	'''获取记录最和
	'p_field:要获取的字段名
	Public Function Sum(Byval p_field)
		Dim t_row
		Field("SUM(" & p_field & ") AS sum_" & p_field)
		Set t_row = Find()
		Sum = t_row("sum_" & p_field)
	End Function
	
	'''最后执行的SQL语句
	Public Property Get LastSQL()
		LastSQL = s_sql
	End Property

	'''添加一条记录，并返回自增ID
	'p_dict:要插入的字典
	Public Function [Add](Byval p_dict)
		'On Error Resume Next
		Dim t_rs : Set t_rs = Server.CreateObject("Adodb.Recordset")
		Dim t_key, t_sql : t_sql = "SELECT TOP 1 * FROM [" & s_prefix & s_table & "]"
		Call t_rs.Open(t_sql, s_db.Conn, 1, 3)
		t_rs.AddNew()
		For Each t_key In p_dict
			t_rs(t_key) = p_dict(t_key)
		Next
		t_rs.Update()
		[Add] = t_rs(s_auto_field)
		t_rs.Close()
		If Err.number <> 0 Then
			'Dim t_log : t_log = dbLog_("插入记录时发生错误!")
			'Console.Warn(t_log)
			Err.Clear()
			[Add] = 0
		End If
	End Function
	
	'''添加一条记录，并返回自增ID
	'p_dict:要插入的字典
	Public Function [Add2](Byval p_dict)
		'On Error Resume Next

		'''todo 此处应修改为原生SQL语句
		Dim t_str_key, t_str_val, t_key, t_val
		t_str_key = ""
		t_str_val = ""
		For Each t_key In p_dict
			If t_str_key = "" Then
				t_str_key = "[" & t_key & "]"
			Else
				t_str_key = t_str_key & ",[" & t_key & "]"
			End If
			Select Case TypeName(p_dict(t_key))
			Case "Byte", "Integer", "Long", "Single", "Double", "Currency", "Decimal"
				'数字型
				t_val = CStr(p_dict(t_key))
			Case "Date"
				'时间型
				t_val = "'" & CStr(p_dict(t_key)) & "'"
			Case "String"
				'字符串
				t_val = "'" & CStr(Replace(p_dict(t_key),"'","''")) & "'"
			Case "Boolean"
				t_val = CStr(p_dict(t_key))
			Case "Null"
				t_val = "NULL"
			Case Else
				Die("更新记录时发生错误，不允许的参数类型" & TypeName(p_dict(t_key)))
			End Select
			If t_str_val = "" Then
				t_str_val = t_val
			Else
				t_str_val = t_str_val & "," & t_val
			End If
		Next

		s_sql = "INSERT INTO [" & s_prefix & s_table & "] (" & t_str_key & ") VALUES (" & t_str_val & ")"
		's_db.Execute(s_sql)
		Dim t_rs : Set t_rs = s_db.Query( s_sql & " SELECT SCOPE_IDENTITY()")
		[Add] = t_rs(0)
		t_rs.Close()
		If Err.number <> 0 Then
			'Dim t_log : t_log = dbLog_("插入记录时发生错误!")
			'Console.Warn(t_log)
			Err.Clear()
			[Add] = 0
		End If
	End Function

	'''更新记录，并返回受影响行数
	'p_dict:要更新的键值对
	Public Function [Update](Byval p_dict)
		'判断各参数类型
		Dim t_a, t_k, t_u : t_u = ""
		For Each t_k In p_dict
			t_a = ""
			Select Case TypeName(p_dict(t_k))
			Case "Byte", "Integer", "Long", "Single", "Double", "Currency", "Decimal"
				'数字型
				t_a = t_k & " = " & CStr(p_dict(t_k))
			Case "Date"
				'时间型
				t_a = t_k & " = #" & CStr(p_dict(t_k)) & "#"
			Case "String"
				'字符串
				t_a = t_k & " = '" & CStr(Replace(p_dict(t_k),"'","''")) & "'"
			Case "Boolean"
				t_a = t_k & " = " & CStr(p_dict(t_k))
			Case "Null"
				t_a = t_k & " = NULL"
			Case Else
				Die("更新记录时发生错误，不允许的参数类型" & TypeName(p_dict(t_k)))
			End Select
			If t_u = "" Then
				t_u = t_a
			Else
				t_u = t_u & ", " & t_a
			End If
		Next
		If s_where = "" Then
			s_sql = "UPDATE [" & s_prefix & s_table & "] SET " & t_u
		Else
			s_sql = "UPDATE [" & s_prefix & s_table & "] SET " & t_u & " WHERE " & s_where
		End If
		[Update] = s_db.Execute(s_sql)
	End Function
	
	'''删除记录
	Public Function [Delete]()
		If s_where = "" Then
			s_sql = "DELETE FROM [" & s_prefix & s_table & "]"
		Else
			s_sql = "DELETE FROM [" & s_prefix & s_table & "] WHERE " & s_where
		End If
		[Delete] = s_db.Execute(s_sql)
	End Function

	'''调用一个MSSQL存储过程并返回数据
	'''todo:此方法暂未测试
	'p_n:存储过程名
	'p_p:存储过程类型
	Public Function DoSP(Byval p_n, Byval p_p)
		'On Error Resume Next
		Dim t_p, t_t, t_c, t_o, t_i, t_r : t_t = ""
		t_p = Param(p_n)
		If Has(t_p(1)) Then
			t_t = UCase(Trim(t_p(1)))
			p_n = Trim(t_p(0))
			t_p = ""
		End If
		Set t_c = Server.CreateObject( s_cfg.Environment("Adodb.Command") )
		With t_c
			.ActiveConnection = s_conn
			.CommandText = p_n
			.CommandType = 4
			.Prepared = true
			.Parameters.append .CreateParameter("return",3,4)
			t_o = "return"
			If Not IsArray(p_p) Then
				If p_p<>"" Then
					p_p = IIF(Instr(p_p,",")>0, p_p = Split(p_p,","), Array(p_p))
				End If
			End If
			If IsArray(p_p) Then
				For t_i = 0 To Ubound(p_p)
					Dim t_pn, t_pv
					If (t_t = "1" Or t_t = "OUT" Or t_t = "3" Or t_t = "ALL") And Instr(p_p(t_i),"@@")=1 Then
						.Parameters.append .CreateParameter(p_p(t_i),200,2,8000)
						t_o = t_o & "," & p_p(t_i)
					Else
						If Instr(p_p(t_i),"@")=1 And Instr(p_p(t_i),":")>2 Then
							t_pn = Left(p_p(t_i),Instr(p_p(t_i),":")-1)
							t_o = t_o & "," & t_pn
							t_pv = Mid(p_p(t_i),Instr(p_p(t_i),":")+1)
							If t_pv = "" Then
								t_pv = NULL
							End If
							.Parameters.append .CreateParameter(t_pn,200,1,8000,t_pv)
						Else
							.Parameters.append .CreateParameter("@param"&(t_i+1),200,1,8000,p_p(t_i))
							t_o = t_o & "," & "@param"&(t_i+1)
						End If
					End If
				Next
			End If
		End With
		t_o = IIF(Instr(t_o,",")>0, Split(t_o,","), Array(t_o))
		If t_t = "1" Or t_t = "OUT" Then
			t_c.Execute : DoSP = t_c
		ElseIf t_t = "2" Or t_t = "RS" Then
			Set DoSP = t_c.Execute
		ElseIf t_t = "3" Or t_t = "ALL" Then
			Dim t_no : Set t_no = Server.CreateObject(C_DICT_NAME)
			Set t_r = t_c.Execute : t_r.close
			For t_i = 0 To Ubound(t_o)
				t_no(Trim(t_o(t_i))) = t_c(t_i)
			Next
			t_r.Open : DoSP = Array(t_r,t_no)
			Set t_no = Nothing
		Else
			t_c.Execute : DoSP = t_c(0)
		End If
		'Console.DbQueryTimes = Console.DbQueryTimes + 1
		Set t_c = Nothing
		If Err.number <> 0 Then
			'出错处理
			'Easp.Error.Raise 24
		End If
		Err.Clear()
		'Console.DbQueryTimes = Console.DbQueryTimes + 1
	End Function
		
End Class
%>